package com.tydic.oa.mixture;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class Testdbcomments {

	public static void main(String[] args){
		Connection connection = null;
		PreparedStatement pstmt = null;
		
		//?????????????????????????????
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (Exception e3) {
			log("?????????"+e3.getMessage());
			return ;
		}
		String url = "jdbc:oracle:thin:@HAWK:1521:orcl";
		try {
			connection = DriverManager.getConnection(url, "emc", "emc");
		} catch (SQLException e3) {
			log("??????????????"+e3.getMessage());
			return ;
		}
		String sql = "";
		String str = "";
		ResultSet rs =null;
		//???????data.txt?????
		FileReader read = null;
		try {
			read = new FileReader("D:/data.txt");
		} catch (FileNotFoundException e2) {
			log("????data.txt???????"+e2.getMessage());
			return ;
		}
		BufferedReader br = new BufferedReader(read);
		//?????????д?????result.txt??
		File file = new File("D:/result.txt");
		FileWriter writer = null;
		try {
			writer = new FileWriter(file);
		} catch (IOException e1) {
			log("????result.txt???????"+e1.getMessage());
			return ;
		}
		String row;
		try {
			while ((row = br.readLine()) != null) {
				row = row.toUpperCase();
				//??????sql???
				sql = "select distinct column_name,data_type,data_length,nullable from all_tab_columns where table_name=?";
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, row);
				rs = pstmt.executeQuery();
				String temp ="" ;
				if(!rs.next())
					log("??"+row+"???????в??????");
				while(rs.next()) {
					if("N".equals(rs.getString(4)))
							temp += String.format("%20s",rs.getString(1))+String.format("%30s",rs.getString(2))+"("+rs.getString(3)+")"+" not null "+",\n";
					else
						temp += String.format("%20s",rs.getString(1))+String.format("%30s",rs.getString(2))+"("+rs.getString(3)+")"+",\n";
					if("DATE".equals(rs.getString(2))||"BLOB".equals(rs.getString(2))||"CLOB".equals(rs.getString(2)))
						temp += String.format("%20s",rs.getString(1))+String.format("%30s",rs.getString(2))+",\n";
				}
				if(temp!=""){
					str = "CREATE TABLE "+row+"\n(\n";
					str = str+temp.substring(0,temp.length()-2)+"\n);";
				}
				System.out.println(str);
				writer.write(str+"\n");
				rs.close();
				pstmt.close();
				
				//????????sql???
				sql = "SELECT B.CONSTRAINT_NAME,A.COLUMN_NAME FROM all_CONS_COLUMNS A, all_CONSTRAINTS B WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME = ? AND B.CONSTRAINT_TYPE = 'P'";
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, row);
				rs = pstmt.executeQuery();
				temp = "";
				str = "";
				while(rs.next()){
					str = "alter table "+row+" add constraint "+rs.getString(1)+" primary key ("+rs.getString(2)+");\n";
					System.out.println(str);
					writer.write(str+"\n");
				}
				rs.close();
				pstmt.close();
				
				//????????sql???
				sql = "select a.index_name,b.column_name from all_indexes a,all_ind_columns b where a.index_name=b.index_name and a.table_name=?";
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, row);
				rs = pstmt.executeQuery();
				temp = "";
				str = "";
				while(rs.next()){
					str = "create index "+rs.getString(1)+" on "+row+" ("+rs.getString(2)+");\n";
					System.out.println(str);
					writer.write(str+"\n");
				}
				rs.close();
				pstmt.close();
				
				//????????sql???
				sql = "select column_name,comments from ALL_COL_COMMENTS where table_name=?";
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, row);
				rs = pstmt.executeQuery();
				temp = "";
				str = "";
				while(rs.next()){
					if(rs.getString(2)!=null){
						str += "comment on column "+row+"."+rs.getString(1)+" is '"+rs.getString(2)+"';\n";
					}
				}
				System.out.println(str);
				writer.write(str+"\n");
				rs.close();
				pstmt.close();
			}
		} catch (IOException e) {
			log("???????"+e.getMessage());
			return ;
		} catch (SQLException e) {
			log("???sql????"+e.getMessage());
			return ;
		}
		try {
			writer.close();
		} catch (IOException e) {
			log("???writer???????"+e.getMessage());
			return ;
		}
	}

	//???????????
	private static void log(String message) {
		//????????????????????????????????log.txt??
		File logFile = new File("D:/log.txt");
		FileWriter logWriter = null;
		try {
			logWriter = new FileWriter(logFile);
			logWriter.write(message);
		} catch (IOException e1) {
			System.out.println("??????????????"+e1.getMessage());
			return ;
		}
		try {
			logWriter.close();
		} catch (IOException e) {
			System.out.println("?????????????"+e.getMessage());
			return ;
		}
	}
}
